﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using Interfaces;
using Entities.Model;
using System.IO;
using System.Data.SqlClient;

namespace Entities.DB
{
    public class ProductDBManager : IProductDBManager
    {
        private DataAccessLayer dal;

        public ProductDBManager()
        {
            dal = new DataAccessLayer();
            
        }

        public int CreateProduct(IProducts _Products)
        {
            dal.SetQuery("INSERT INTO [product] ([ProductID], [ProductName], [CurrentStock], [StockUnits], [Category], [Price]) VALUES (@ProductID, @ProductName, @CurrentStock, @StockUnits, @Category, @Price); INSERT INTO [product_info] ([ProductID], [Description], [Comment], [Picture]) VALUES (@ProductID, @Description, @Comment, @Picture)");

            dal.AddParameter("@ProductID", _Products.ProductID, System.Data.DbType.String);
            dal.AddParameter("@ProductName", _Products.ProductName, System.Data.DbType.String);
            dal.AddParameter("@CurrentStock", _Products.CurrentStock, System.Data.DbType.Int32);
            dal.AddParameter("@StockUnits", _Products.StockUnits, System.Data.DbType.String);
            dal.AddParameter("@Category", _Products.Category, System.Data.DbType.String);
            dal.AddParameter("@Price", _Products.Price, System.Data.DbType.Double);
            dal.AddParameter("@Description", _Products.ProductInfo.Description, System.Data.DbType.String);
            dal.AddParameter("@Comment", _Products.ProductInfo.Comment, System.Data.DbType.String);
            if (_Products.ProductInfo.Picture != null)
            {
                dal.AddParameter("@Picture", _Products.ProductInfo.Picture, SqlDbType.VarBinary, int.MaxValue);
            }
            else
            {
                dal.AddParameter("@Picture", DBNull.Value, SqlDbType.VarBinary, int.MaxValue);
            }
            

            return dal.Execute();
        }

        public int UpdateProduct(IProducts _Products)
        {
            dal.SetQuery("UPDATE [product] SET [ProductName] = @ProductName, [CurrentStock] = @CurrentStock, [StockUnits] = @StockUnits, [Category] = @Category, [Price] = @Price WHERE ProductID = @ProductID; UPDATE [product_info] SET [Description] = @Description, [Comment] = @Comment, [Picture] = @Picture WHERE ProductID = @ProductID");

            dal.AddParameter("@ProductID", _Products.ProductID, System.Data.DbType.String);
            dal.AddParameter("@ProductName", _Products.ProductName, System.Data.DbType.String);
            dal.AddParameter("@CurrentStock", _Products.CurrentStock, System.Data.DbType.Int32);
            dal.AddParameter("@StockUnits", _Products.StockUnits, System.Data.DbType.String);
            dal.AddParameter("@Category", _Products.Category, System.Data.DbType.String);
            dal.AddParameter("@Price", _Products.Price, System.Data.DbType.Double);
            dal.AddParameter("@Description", _Products.ProductInfo.Description, System.Data.DbType.String);
            dal.AddParameter("@Comment", _Products.ProductInfo.Comment, System.Data.DbType.String);
            if (_Products.ProductInfo.Picture != null)
            {
                dal.AddParameter("@Picture", _Products.ProductInfo.Picture, SqlDbType.VarBinary, int.MaxValue);
            }
            else
            {
                dal.AddParameter("@Picture", DBNull.Value, SqlDbType.VarBinary, int.MaxValue);
            }

            return dal.Execute();
        }

        public int DeleteProduct(string _Product_id)
        {
            dal.SetQuery("DELETE FROM [product_info] WHERE ProductID = @ProductID; DELETE FROM [product] WHERE ProductID = @ProductID");
            dal.AddParameter("@ProductID", _Product_id, System.Data.DbType.String);
            return dal.Execute();
        }

        public IProducts GetProductByID(string _Product_id)
        {
            IProducts product = null;

            dal.SetQuery("SELECT * FROM [product] LEFT JOIN [product_info] ON ([product].ProductID = [product_info].ProductID) WHERE [product].ProductID = @ProductID");
            dal.AddParameter("@ProductID", _Product_id, System.Data.DbType.String);
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    product = ReadProducts(reader);
                }
            }
            else
            {
                return null;
            }
            return product;
        }

        public IList<IProducts> GetProductByCategory(string _Category)
        {
            IList<IProducts> result = new List<IProducts>();
            IProducts product = null;

            dal.SetQuery("SELECT * FROM [product] LEFT JOIN [product_info] ON ([product].ProductID = [product_info].ProductID) WHERE [product].Category = @Category");
            dal.AddParameter("@Category", _Category, System.Data.DbType.String);
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    product = ReadProducts(reader);
                    result.Add(product);
                }
            }
            else
            {
                return null;
            }
            return result;
        }

        public IList<IProducts> GetAllProducts()
        {
            IList<IProducts> result = new List<IProducts>();
            IProducts product = null;

            dal.SetQuery("SELECT * FROM [product] LEFT JOIN [product_info] ON ([product].ProductID = [product_info].ProductID)");
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    product = ReadProducts(reader);
                    result.Add(product);
                }
            }
            else
            {
                return null;
            }
            return result;
        }

        private IProducts ReadProducts(SqlDataReader reader)
        {
            IProducts product = new Products();

            product.ProductID = reader["ProductID"].ToString();
            product.ProductName = reader["ProductName"].ToString();
            product.CurrentStock = Convert.ToInt32(reader["CurrentStock"].ToString());
            product.StockUnits = reader["StockUnits"].ToString();
            product.Category = reader["Category"].ToString();
            product.Price = Convert.ToDouble(reader["Price"].ToString());

            product.ProductInfo = new Product_Info();
            product.ProductInfo.Description = reader["Description"].ToString();
            product.ProductInfo.Comment = reader["Comment"].ToString();

            if (reader["Picture"].ToString() != "")
            {
                product.ProductInfo.Picture = (byte[])reader["Picture"];
            }
            else
            {
                product.ProductInfo.Picture = null;
            }

            return product;
        }
    }
}
